﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using QuanLyHocPhi.BLL;
using QuanLyHocPhi.Model;
using QuanLyHocPhi.GUI.DuLieu;
using System.IO;
using HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
namespace QuanLyHocPhi.GUI.TacNghiep
{
    public partial class FormThongKeTheoLop : Form
    {
        private string fileName = string.Format("{0}\\Documents\\TEMPLATES\\MauThongKeDSTheoLop.xls",
        Application.StartupPath);
        private string fileName2 = string.Format("{0}\\Documents\\TEMPLATES\\MauThongKeTheoLop.xls",
        Application.StartupPath);
        bool lockUpdate = false;
        bool allowReport = false;
        
        
        public FormThongKeTheoLop()
        {
            InitializeComponent();
            allowReport = false;
            LoadNamHoc();
            cmbHocKy.SelectedIndex = 2;

            #region Cấu hình lưới lớp gốc
            colMaLop.DataPropertyName = "MaLopGoc";
            colTenLop.DataPropertyName = "TenLopGoc";
            gridLopGoc.AutoGenerateColumns = false;
            gridLopGoc.AllowDrop = false;
            gridLopGoc.AllowUserToAddRows = false;
            gridLopGoc.AllowUserToDeleteRows = false;
            #endregion

            #region Cấu hình lưới danh sách sinh viên
            colHK1.DataPropertyName = "HK1";
            colHK2.DataPropertyName = "HK2";
            colHocPhi.DataPropertyName = "strLeHocPhi";
            colNo.DataPropertyName = "strConNo";
            colMSV.DataPropertyName = "MaSinhVien";
            colHoDem.DataPropertyName = "HoDem";
            colTen.DataPropertyName = "Ten";
            gridDanhSachSinhVien.AutoGenerateColumns = false;
            gridDanhSachSinhVien.AllowDrop = false;
            gridDanhSachSinhVien.AllowUserToAddRows = false;
            gridDanhSachSinhVien.AllowUserToDeleteRows = false;
            #endregion

            ShowHideColumn();

            LoadLoaiHinh();
        }

        private void InitComboSuggestion(ComboBox cmb, object datasource, string displayMember, string valueMember, string[] rangeDisplay)
        {
            cmb.AutoCompleteSource = AutoCompleteSource.CustomSource;
            cmb.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            cmb.DataSource = datasource;
            if (displayMember != null)
                cmb.DisplayMember = displayMember;
            if (valueMember != null)
                cmb.ValueMember = valueMember;
            // Xác định AutoCompleteCustomeSource
            var autoCompletSet = new AutoCompleteStringCollection();
            autoCompletSet.AddRange(rangeDisplay);
            cmb.AutoCompleteCustomSource = autoCompletSet;

            // Các tinh chỉnh nâng cao
            cmb.Leave += (oSender, oE) =>
            {
                var cbx = oSender as ComboBox;
                if (cbx == null)
                    return;
                cbx.BackColor = cbx.SelectedItem == null ? Color.LightPink : SystemColors.Window;
            };

            cmb.KeyDown += (oSender, oE) =>
            {
                if (oE.KeyCode != Keys.Enter)
                    return;
                var cbx = oSender as ComboBox;
                if (cbx == null)
                    return;
                cbx.BackColor = cbx.SelectedItem == null ? Color.LightPink : SystemColors.Window;
            };
        }

        private void ShowHideColumn()
        {

        }

        private void LoadLoaiHinh()
        {
            lockUpdate = true;
            var ds = BizLoaiHinhDaoTao.GetListLoaiHinh();
            InitComboSuggestion(cmbLoaiHinh, ds, "TenLoaiHinh", "MaLoaiHinh", ds.Select(e => e.TenLoaiHinh).ToArray());
            lockUpdate = false;
            LoadNamTuyenSinh();
        }

        private void LoadNamTuyenSinh()
        {
            lockUpdate = true;
            cmbNamTuyenSinh.DataSource = null;
            if ((cmbLoaiHinh.SelectedItem as LoaiHinhDaoTao) != null)
            {
                var ds = BizLoaiHinhDaoTao.GetList_NamTuyenSinh((cmbLoaiHinh.SelectedItem as LoaiHinhDaoTao).MaLoaiHinh);
                InitComboSuggestion(cmbNamTuyenSinh, ds, null, null, ds.ToArray());
                //cmbNamTuyenSinh.DataSource = ds;
                lockUpdate = false;
                LoadLopGoc();
            }
        }

        private void LoadNamHoc()
        {
            var lstNamHoc = BizNamHoc.GetListNamHoc();
            cmbNamHoc.DataSource = lstNamHoc;
        }

        private void LoadLopGoc()
        {
            lockUpdate = true;
            bdsLop.DataSource = null;
            gridLopGoc.DataSource = bdsLop;
            var ds = BizLopGoc.GetListLopGoc((cmbLoaiHinh.SelectedItem as LoaiHinhDaoTao).MaLoaiHinh, cmbNamTuyenSinh.SelectedItem as string);
            bdsLop.DataSource = ds;
            gridLopGoc.DataSource = bdsLop;
            lbSoLop.Text = ds.Count.ToString() + " lớp";
            lbSoSinhVien.Text = "0 sinh viên";
            lockUpdate = false;
            LoadDanhSachThongKeLop();
        }

        private void cmbLoaiHinh_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!lockUpdate)
            {
                LoadNamTuyenSinh();
            }
        }

        private void cmbNamTuyenSinh_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!lockUpdate)
            {
                LoadLopGoc();
            }
        }

        private void bdsLop_CurrentChanged(object sender, EventArgs e)
        {
            if (!lockUpdate)
                LoadDanhSachThongKeLop();
        }

        private void LoadDanhSachThongKeLop()
        {
            colHocPhi.HeaderText = rbHocPhi.Checked ? "Học phí" : "Lệ phí";
            if (bdsLop.Current != null)
            {

                var selectedLop = bdsLop.Current as LopGoc;
                var ds = new List<ThongKeLop>();
                var namHoc = cmbNamHoc.SelectedValue.ToString();
                int hk = cmbHocKy.SelectedIndex;
                var laHocPhi = rbHocPhi.Checked;
                var frm = new FormProgress();
                frm.DoWork += (oSender, oE) =>
                {
                    ds = BizPhieuThu.GetThongKeTheoLop(selectedLop.MaLopGoc, namHoc, hk, laHocPhi);
                };

                frm.RunWorkerCompleted += (oSender, oE) =>
                {
                    allowReport = true;
                };
                frm.StartWorking(this);
                ShowHideColumn();
                bdsDanhSachSinhVien.DataSource = ds;
                gridDanhSachSinhVien.DataSource = bdsDanhSachSinhVien;
                lbSoSinhVien.Text = ds.Count.ToString() + " sinh viên";
            }
            else
            {
                bdsDanhSachSinhVien.DataSource = null;
                gridDanhSachSinhVien.DataSource = bdsDanhSachSinhVien;
            }
        }

        private void ndSoHocKy_ValueChanged(object sender, EventArgs e)
        {
            ShowHideColumn();
            gridDanhSachSinhVien.Refresh();
        }

        private void bdsDanhSachSinhVien_DataSourceChanged(object sender, EventArgs e)
        {
            if (bdsDanhSachSinhVien.DataSource != null)
                bntXuatRaFile.Enabled = true;
            else
                bntXuatRaFile.Enabled = false;
        }

        private void bntXuatRaFile_Click(object sender, EventArgs e)
        {

            ReportViewer(false);
        }

        void ReportViewer(bool chiXuatNo, bool excelExport = false)
        {
            var danhSach = bdsDanhSachSinhVien.DataSource as List<ThongKeLop>;
            if (danhSach != null && danhSach.Count > 0 && chiXuatNo)
                danhSach = danhSach.Where(e => e.ConNo > 0).ToList(); ;
            var soLuong = danhSach.Count;
            string tenLoaiHinh = (cmbLoaiHinh.SelectedItem as LoaiHinhDaoTao).TenLoaiHinh;
            string tenLopGoc = (bdsLop.Current as LopGoc).TenLopGoc;
            int hk = cmbHocKy.SelectedIndex;
            var namHoc = cmbNamHoc.SelectedValue.ToString();
            bool duLieuRong = soLuong == 0 ? true : false;
            if (!excelExport)
            {
                //Sử dụng report viewer
                if (!duLieuRong)
                {
                    FormInBangThongKeTheoLop formIn = new FormInBangThongKeTheoLop();
                    formIn.LoadReportData(danhSach, tenLopGoc, namHoc, danhSach.Sum(e => e.ConNo).ToString().FormatCurrency(), rbHocPhi.Checked);
                }
            }
            else
            {
                //Đoạn dưới này sử dụng NPOI để xuất ==> Hơi chậm nên không sử dụng nữa
                FileStream fs = null;
                if (duLieuRong)
                    MessageBox.Show("Hiện tại danh sách là rỗng", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                else
                {
                    #region Tiền xử lý
                    if (!File.Exists(fileName))
                    {
                        if (!File.Exists(fileName2))
                        {
                            MessageBox.Show("File mẫu không tồn tại", "Lỗi");
                            return;
                        }
                        try
                        {
                            fs = new FileStream(fileName2, FileMode.Open, FileAccess.Read);
                        }
                        catch (Exception)
                        {
                            MessageBox.Show("File mẫu đang mở. Vui lòng đóng lại trước khi tiếp tục", "Lỗi");
                            return;
                        }
                    }
                    else
                    {
                        try
                        {
                            fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                        }
                        catch (Exception)
                        {
                            MessageBox.Show("File mẫu đang mở. Vui lòng đóng lại trước khi tiếp tục", "Lỗi");
                            return;
                        }
                    }
                    #endregion

                    #region Làm việc với file excel

                    FormProgress frm = new FormProgress();
                    frm.DoWork += delegate(object oSender, DoWorkEventArgs oE)
                    {
                        HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);

                        #region Tạo style cho thông tin cs nhân
                        var titleCellStyle = templateWorkbook.CreateCellStyle();
                        titleCellStyle.Alignment = HorizontalAlignment.CENTER;
                        var titleFont = templateWorkbook.CreateFont();
                        titleFont.Boldweight = (short)FontBoldWeight.BOLD;
                        titleFont.FontHeight = 236;
                        titleFont.FontName = "Times New Roman";
                        titleCellStyle.SetFont(titleFont);
                        #endregion

                        #region Tạo các style cho phần hiển thị nội dung
                        var detailCellStyle = templateWorkbook.CreateCellStyle();
                        detailCellStyle.Alignment = HorizontalAlignment.CENTER;
                        detailCellStyle.BorderBottom = CellBorderType.DASHED;
                        detailCellStyle.BorderLeft = CellBorderType.THIN;
                        detailCellStyle.BorderRight = CellBorderType.THIN;
                        var cellHoStyle = templateWorkbook.CreateCellStyle();
                        cellHoStyle.Alignment = HorizontalAlignment.LEFT;
                        cellHoStyle.BorderBottom = CellBorderType.DASHED;
                        cellHoStyle.BorderLeft = CellBorderType.THIN;
                        var cellTenStyle = templateWorkbook.CreateCellStyle();
                        cellTenStyle.Alignment = HorizontalAlignment.RIGHT;
                        cellTenStyle.BorderBottom = CellBorderType.DASHED;
                        cellTenStyle.BorderRight = CellBorderType.THIN;

                        var detailCellStyleEnd = templateWorkbook.CreateCellStyle();
                        detailCellStyleEnd.Alignment = HorizontalAlignment.CENTER;
                        detailCellStyleEnd.BorderBottom = CellBorderType.THIN;
                        detailCellStyleEnd.BorderLeft = CellBorderType.THIN;
                        detailCellStyleEnd.BorderRight = CellBorderType.THIN;
                        var cellHoStyleEnd = templateWorkbook.CreateCellStyle();
                        cellHoStyleEnd.Alignment = HorizontalAlignment.LEFT;
                        cellHoStyleEnd.BorderBottom = CellBorderType.THIN;
                        cellHoStyleEnd.BorderLeft = CellBorderType.THIN;
                        var cellTenStyleEnd = templateWorkbook.CreateCellStyle();
                        cellTenStyleEnd.Alignment = HorizontalAlignment.RIGHT;
                        cellTenStyleEnd.BorderBottom = CellBorderType.THIN;
                        cellTenStyleEnd.BorderRight = CellBorderType.THIN;

                        var cellTongSoStyle = templateWorkbook.CreateCellStyle();
                        var fontCellTongSo = templateWorkbook.CreateFont();
                        fontCellTongSo.IsItalic = true;
                        cellTongSoStyle.SetFont(fontCellTongSo);
                        var cellBottomStyle = templateWorkbook.CreateCellStyle();
                        cellBottomStyle.Alignment = HorizontalAlignment.CENTER;
                        var fontCellBottomStyle = templateWorkbook.CreateFont();
                        fontCellBottomStyle.Boldweight = (short)FontBoldWeight.BOLD;
                        cellBottomStyle.SetFont(fontCellBottomStyle);

                        var tieuDeStyle = templateWorkbook.CreateCellStyle();
                        tieuDeStyle.Alignment = HorizontalAlignment.CENTER;
                        tieuDeStyle.BorderBottom = CellBorderType.THIN;
                        tieuDeStyle.BorderLeft = CellBorderType.THIN;
                        tieuDeStyle.BorderRight = CellBorderType.THIN;
                        tieuDeStyle.BorderTop = CellBorderType.THIN;
                        tieuDeStyle.SetFont(fontCellBottomStyle);
                        #endregion

                        #region Khởi tạo
                        var sheet = templateWorkbook.GetSheet("sheet1");
                        var indexRow = 3;
                        var row = sheet.GetRow(indexRow);
                        #endregion

                        #region Chèn phần thông tin tiêu đề
                        Cell cell;
                        try
                        {
                            cell = row.CreateCell(0);
                        }
                        catch
                        {
                            cell = row.GetCell(0);
                        }
                        cell.SetCellValue(string.Format("DANH SÁCH SINH VIÊN LỚP {0} CÒN NỢ {1}", tenLopGoc, rbHocPhi.Checked ? "HỌC PHÍ" : "LỆ PHÍ"));

                        //cell.CellStyle = titleCellStyle;
                        indexRow++;
                        row = sheet.GetRow(indexRow);
                        try
                        {
                            cell = row.CreateCell(0);
                        }
                        catch
                        {
                            cell = row.GetCell(0);
                        }
                        if (hk == 0)
                            cell.SetCellValue(string.Format("HỌC KỲ I, NĂM HỌC {0} (Tính đến ngày {1:dd/MM/yyyy})", namHoc, DateTime.Now));
                        else if (hk == 1)
                            cell.SetCellValue(string.Format("HỌC KỲ II, NĂM HỌC {0} (Tính đến ngày {1:dd/MM/yyyy})", namHoc, DateTime.Now));
                        else
                            cell.SetCellValue(string.Format("NĂM HỌC {0} (Tính đến ngày {1:dd/MM/yyyy})", namHoc, DateTime.Now));
                        //cell.CellStyle = titleCellStyle;

                        #endregion

                        #region Chèn tiêu đề
                        indexRow += 2;
                        row = sheet.GetRow(indexRow);
                        //for (int i = 0; i < ndSoHocKy.Value; i++)
                        //{
                        //    try
                        //    {
                        //        cell = row.CreateCell(i + 4);
                        //    }
                        //    catch
                        //    {
                        //        cell = row.GetCell(i + 4);
                        //    }
                        //    cell.SetCellValue("Học kỳ " + (i + 1).ToString());
                        //    cell.CellStyle = tieuDeStyle;
                        //}
                        #endregion

                        #region Chèn nội dung

                        for (int j = 0; j < soLuong; j++)
                        {
                            var dtRow = danhSach[j];
                            row = sheet.CreateRow(j + 7);
                            cell = row.CreateCell(0);
                            cell.SetCellValue(j + 1);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                            cell = row.CreateCell(1);
                            cell.SetCellValue(dtRow.MaSinhVien);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                            cell = row.CreateCell(2);
                            cell.SetCellValue(dtRow.HoDem);
                            cell.CellStyle = (j == (soLuong - 1)) ? cellHoStyleEnd : cellHoStyle;
                            cell = row.CreateCell(3);
                            cell.SetCellValue(dtRow.Ten);
                            cell.CellStyle = (j == (soLuong - 1)) ? cellTenStyleEnd : cellTenStyle;
                            cell = row.CreateCell(4);
                            cell.SetCellValue(tenLopGoc);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                            cell = row.CreateCell(5);
                            cell.SetCellValue(dtRow.strConNo);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                            cell = row.CreateCell(6);
                            cell.SetCellValue(dtRow.strHinhThucNop);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                            cell = row.CreateCell(7);
                            cell.SetCellValue(dtRow.Dien);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                            cell = row.CreateCell(8);
                            cell.SetCellValue(dtRow.DoiTuong);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                            cell = row.CreateCell(9);
                            cell.SetCellValue(dtRow.GhiChu);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                        }
                        #endregion

                        #region Chèn phần bottom
                        indexRow = soLuong + 7;
                        row = sheet.GetRow(indexRow);

                        try
                        {
                            cell = row.CreateCell(4);
                        }
                        catch (Exception)
                        {
                            cell = row.GetCell(4);
                        }
                        cell.SetCellValue("Tổng: ");
                        cell.CellStyle = titleCellStyle;

                        try
                        {
                            cell = row.CreateCell(5);
                        }
                        catch (Exception)
                        {
                            cell = row.GetCell(5);
                        }
                        cell.SetCellValue(danhSach.Sum(e => e.ConNo).ToString().FormatCurrency());
                        cell.CellStyle = titleCellStyle;

                        indexRow += 2;
                        row = sheet.GetRow(indexRow);
                        try
                        {
                            cell = row.CreateCell(5);
                        }
                        catch (Exception)
                        {

                            cell = row.GetCell(5);
                        }
                        cell.SetCellValue(string.Format("Huế, ngày {0:dd} tháng {0:MM} năm {0:yyyy}", DateTime.Now));
                        cell.CellStyle = titleCellStyle;

                        indexRow++;
                        row = sheet.GetRow(indexRow);
                        try
                        {
                            cell = row.CreateCell(2);
                        }
                        catch (Exception)
                        {

                            cell = row.GetCell(2);
                        }
                        cell.SetCellValue(string.Format("Người lập"));
                        cell.CellStyle = titleCellStyle;
                        try
                        {
                            cell = row.CreateCell(5);
                        }
                        catch (Exception)
                        {

                            cell = row.GetCell(5);
                        }
                        cell.SetCellValue(string.Format("Trưởng Phòng KH - TC"));
                        cell.CellStyle = titleCellStyle;

                        indexRow += 6;
                        row = sheet.GetRow(indexRow);
                        var thongtin = BizNguoiSuDung.ThongTinDangKy();
                        if (thongtin != null && thongtin.Length == 2)
                        {
                            try
                            {
                                cell = row.CreateCell(2);
                            }
                            catch (Exception)
                            {

                                cell = row.GetCell(2);
                            }
                            cell.SetCellValue(Program.NguoiSuDung.HoTen);
                            cell.CellStyle = cellBottomStyle;

                            try
                            {
                                cell = row.CreateCell(5);
                            }
                            catch (Exception)
                            {

                                cell = row.GetCell(5);
                            }
                            cell.SetCellValue(thongtin[0]);
                            cell.CellStyle = cellBottomStyle;
                        }
                        #endregion

                        #region Lưu ra file Excel mới
                        sheet.ForceFormulaRecalculation = true;
                        if (!Directory.Exists(string.Format("{0}\\Temp", Application.StartupPath)))
                            Directory.CreateDirectory(string.Format("{0}\\Temp", Application.StartupPath));
                        var tenfile = string.Format("{0}\\Temp\\ThongKeLop_{2}_{1:dd_MM_yyyy_hh_mm_ss}.xls", Application.StartupPath, DateTime.Now, tenLopGoc);
                        try
                        {
                            using (var fileData = new FileStream(tenfile, FileMode.Create))
                            {
                                templateWorkbook.Write(fileData);
                                fileData.Close();

                                #region Excel commented
                                #endregion
                                System.Diagnostics.Process.Start(tenfile);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new Exception(ex.ToString());
                        }
                        fs.Close();
                        #endregion
                    };
                    #endregion

                    #region On complete
                    frm.RunWorkerCompleted += delegate(object oSender, RunWorkerCompletedEventArgs oE)
                    {
                        if (oE.Error != null)
                        {
                            string msg = oE.Error.Message;
                            if (oE.Error.InnerException != null)
                                msg += oE.Error.InnerException.Message;
                            DialogUtils.ShowError(msg);
                        }

                    };
                    #endregion

                    frm.StartWorking(this);
                }
            }
        }

        private void cmbHocKy_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbHocKy.SelectedIndex != -1)
            {
                colHK1.Visible = true;
                colHK2.Visible = true;
                if (cmbHocKy.SelectedIndex == 0)
                {
                    colHK2.Visible = false;
                }
                else if (cmbHocKy.SelectedIndex == 1)
                {
                    colHK1.Visible = false;
                }
                if (allowReport)
                    LoadDanhSachThongKeLop();
            }
        }

        private void cmbNamHoc_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (allowReport)
                LoadDanhSachThongKeLop();
        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            ReportViewer(true);
        }

        private void rbHocPhi_CheckedChanged(object sender, EventArgs e)
        {
            if (allowReport)
                LoadDanhSachThongKeLop();
        }

        private void btnThongKe_Click(object sender, EventArgs e)
        {

        }

        private void bdsDanhSachSinhVien_CurrentChanged(object sender, EventArgs e)
        {

        }

        private void rbLePhi_CheckedChanged(object sender, EventArgs e)
        {

        }
    }
}
